package com.gitee.fsmxm.ocon.util;

import com.gitee.fsmxm.ocon.constant.XlCorruptLoad;
import com.gitee.fsmxm.ocon.constant.XlPlatform;

import java.util.Locale;

import static com.gitee.fsmxm.ocon.constant.XlFileFormat.*;

/**
 * @author fsmxm
 */
public class ExcelUtils {

    private ExcelUtils() {
    }

    /**
     * paramOfOpen
     *
     * @param fileName 文件名
     * @return paramOfOpen
     * @see ExcelUtils#paramOfOpen(String, Integer, Boolean, Object, String, String, Boolean, Integer, Character, Boolean, Boolean, Object, Boolean, Boolean, Integer)
     */
    public static Object[] paramOfOpen(String fileName) {
        return paramOfOpen(
                fileName,
                1,
                true,
                null,
                null,
                null,
                true,
                null,
                null,
                null,
                false,
                null,
                false,
                true,
                null);
    }

    /**
     * params of Opens a workbook.
     *
     * @param fileName                  Optional. The file name of the workbook to be opened.
     * @param updateLinks               Optional. Specifies the way external references (links) in the file,
     *                                  such as the reference to a range in the Budget.
     *                                  xls workbook in the following formula =SUM([Budget.xls]Annual!C10:C25), are updated.
     *                                  If this argument is omitted, the user is prompted to specify how links will be updated.
     *                                  For more information about the values used by this parameter, see the Remarks section.
     *                                  If Microsoft Excel is opening a file in the WKS, WK1,
     *                                  or WK3 format and the UpdateLinks argument is 0, no charts are created;
     *                                  otherwise, Microsoft Excel generates charts from the graphs attached to the file.
     * @param readOnly                  Optional. True to open the workbook in read-only mode.
     * @param format                    Optional. If Microsoft Excel opens a text file,
     *                                  this argument specifies the delimiter character.
     *                                  If this argument is omitted, the current delimiter is used.
     *                                  For more information about the values used by this parameter, see the Remarks section.
     * @param password                  Optional. A string that contains the password required to open a protected workbook.
     *                                  If this argument is omitted and the workbook requires a password,
     *                                  the user is prompted for the password.
     * @param writeResPassword          Optional. A string that contains the password required to write to a write-reserved workbook.
     *                                  If this argument is omitted and the workbook requires a password,
     *                                  the user will be prompted for the password.
     * @param ignoreReadOnlyRecommended Optional. True to have Microsoft Excel not display the read-only recommended
     *                                  message (if the workbook was saved with the Read-Only Recommended option).
     * @param origin                    Optional. If the file is a text file, this argument indicates where it originated,
     *                                  so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly.
     *                                  Can be one of the following {@link XlPlatform} constants: xlMacintosh, xlWindows, or xlMSDOS.
     *                                  If this argument is omitted, the current operating system is used.
     * @param delimiter                 Optional. If the file is a text file and the Format argument is 6,
     *                                  this argument is a string that specifies the character to be used as the delimiter.
     *                                  For example, use Chr(9) for tabs, use "," for commas, use ";" for semicolons,
     *                                  or use a custom character. Only the first character of the string is used.
     * @param editable                  Optional. If the file is a Microsoft Excel 4.0 add-in,
     *                                  this argument is True to open the add-in so that it is a visible window.
     *                                  If this argument is False or omitted, the add-in is opened as hidden,
     *                                  and it cannot be unhidden. This option does not apply to add-ins created in Microsoft Excel 5.0 or later.
     *                                  If the file is an Excel template, True to open the specified template for editing.
     *                                  False to open a new workbook based on the specified template. The default value is False.
     * @param notify                    Optional. If the file cannot be opened in read/write mode,
     *                                  this argument is True to add the file to the file notification list.
     *                                  Microsoft Excel will open the file as read-only, poll the file notification list,
     *                                  and then notify the user when the file becomes available.
     *                                  If this argument is False or omitted, no notification is requested,
     *                                  and any attempts to open an unavailable file will fail.
     * @param converter                 Optional. The index of the first file converter to try when opening the file.
     *                                  The specified file converter is tried first;
     *                                  if this converter does not recognize the file, all other converters are tried.
     *                                  The converter index consists of the row numbers of the converters returned by the FileConverters property.
     * @param addToMru                  Optional. True to add this workbook to the list of recently used files. The default value is False.
     * @param local                     Optional. True saves files against the language of Microsoft Excel (including control panel settings).
     *                                  False (default) saves files against the language of Visual Basic for
     *                                  Applications (VBA) (which is typically United States English unless the VBA project
     *                                  where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).
     * @param corruptLoad               Optional. Can be one of the {@link XlCorruptLoad} constants: xlNormalLoad,
     *                                  xlRepairFile and xlExtractData. The default behavior if no value is specified is xlNormalLoad,
     *                                  and does not attempt recovery when initiated through the OM.
     * @return paramOfOpen
     * @see <a href="https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open">excel.workbooks.open</a>
     */
    public static Object[] paramOfOpen(String fileName,
                                       Integer updateLinks,
                                       Boolean readOnly,
                                       Object format,
                                       String password,
                                       String writeResPassword,
                                       Boolean ignoreReadOnlyRecommended,
                                       Integer origin,
                                       Character delimiter,
                                       Boolean editable,
                                       Boolean notify,
                                       Object converter,
                                       Boolean addToMru,
                                       Boolean local,
                                       Integer corruptLoad) {
        return new Object[]{
                VT.get(fileName),
                VT.get(updateLinks),
                VT.get(readOnly),
                VT.get(format),
                VT.get(password),
                VT.get(writeResPassword),
                VT.get(ignoreReadOnlyRecommended),
                VT.get(origin),
                VT.get(delimiter),
                VT.get(editable),
                VT.get(notify),
                VT.get(converter),
                VT.get(addToMru),
                VT.get(local),
                VT.get(corruptLoad)
        };
    }

    public static Object[] paramOfSaveAs(String fileName, Integer fileFormat) {
        return paramOfSaveAs(
                fileName,
                fileFormat,
                null,
                null,
                null,
                null,
                null,
                3,
                false,
                null,
                null,
                null
        );
    }

    /**
     * params of SaveAs
     *
     * @param fileName            A string that indicates the name of the file to be saved. You can include a full path;
     *                            if you don't, Microsoft Excel saves the file in the current folder.
     * @param fileFormat          The file format to use when you save the file. For a list of valid choices, see the
     *                            XlFileFormat enumeration. For an existing file, the default format is the last file
     *                            format specified; for a new file, the default is the format of the version of Excel
     *                            being used.
     * @param password            A case-sensitive string (no more than 15 characters) that indicates the protection
     *                            password to be given to the file.
     * @param writeResPassword    A string that indicates the write-reservation password for this file. If a file is
     *                            saved with the password and the password isn't supplied when the file is opened, the
     *                            file is opened as read-only.
     * @param readOnlyRecommended True to display a message when the file is opened, recommending that the file be opened
     *                            as read-only.
     * @param createBackup        True to create a backup file.
     * @param accessMode          The access mode for the workbook.
     * @param conflictResolution  An XlSaveConflictResolution value that determines how the method resolves a conflict
     *                            while saving the workbook. If set to xlUserResolution, the conflict-resolution dialog
     *                            box is displayed.
     *                            If set to xlLocalSessionChanges, the local user's changes are automatically accepted.
     *                            If set to xlOtherSessionChanges, the changes from other sessions are automatically
     *                            accepted instead of the local user's changes.
     *                            If this argument is omitted, the conflict-resolution dialog box is displayed.
     * @param addToMru            True to add this workbook to the list of recently used files. The default value is False.
     * @param textCodepage        Ignored for all languages in Microsoft Excel.
     *                            <p>
     *                            NOTE: When Excel saves a workbook to one of the CSV or text formats, which are specified
     *                            by using the FileFormat parameter, it uses the code page that corresponds to the language
     *                            for the system locale in use on the current computer. This system setting is available
     *                            in the Control Panel > Region and Language > Location tab under Current location.
     * @param textVisualLayout    Ignored for all languages in Microsoft Excel.
     *                            <p>
     *                            NOTE: When Excel saves a workbook to one of the CSV or text formats, which are specified
     *                            by using the FileFormat parameter, it saves these formats in logical layout.
     *                            If left-to-right (LTR) text is embedded within right-to-left (RTL) text in the file,
     *                            or vice versa, logical layout saves the contents of the file in the correct reading order
     *                            for all languages in the file without regard to direction. When an application opens the
     *                            file, each run of LTR or RTL characters are rendered in the correct direction according
     *                            to the character value ranges within the code page (unless an application that is designed
     *                            to display the exact memory layout of the file, such as a debugger or editor, is used to
     *                            open the file).
     * @param local               True saves files against the language of Microsoft Excel (including control panel settings).
     *                            False (default) saves files against the language of Visual Basic for Applications (VBA)
     *                            (which is typically US English unless the VBA project where Workbooks.Open is run from
     *                            is an old internationalized XL5/95 VBA project).
     * @return params of SaveAs
     * @see <a href="https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas">excel.workbook.saveas</a>
     */
    public static Object[] paramOfSaveAs(String fileName,
                                         Integer fileFormat,
                                         String password,
                                         String writeResPassword,
                                         Boolean readOnlyRecommended,
                                         Boolean createBackup,
                                         Integer accessMode,
                                         Integer conflictResolution,
                                         Boolean addToMru,
                                         Integer textCodepage,
                                         Integer textVisualLayout,
                                         Boolean local) {
        return new Object[]{
                VT.get(fileName),
                VT.get(fileFormat),
                VT.get(password),
                VT.get(writeResPassword),
                VT.get(readOnlyRecommended),
                VT.get(createBackup),
                VT.get(accessMode),
                VT.get(conflictResolution),
                VT.get(addToMru),
                VT.get(textCodepage),
                VT.get(textVisualLayout),
                VT.get(local)
        };
    }

    public static int getFormatType(String targetExt) {
        switch (targetExt.toLowerCase(Locale.ENGLISH)) {
            case "csv":
                return XL_CSV;
            case "html":
            case "htm":
                return XL_HTML;
            case "mhtml":
            case "mht":
                return XL_WEB_ARCHIVE;
            case "txt":
                return XL_CURRENT_PLATFORM_TEXT;
            case "xls":
                return XL_WORKBOOK_NORMAL;
            case "xlsx":
                return XL_WORKBOOK_DEFAULT;
            case "xml":
                return XL_XML_SPREADSHEET;
            // 绕过验证 pdf 是 ExportAsFixedFormat 而不是 SaveAs
            case "pdf":
                return 111;
            default:
                return -1;
        }
    }

}
